﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;


public static class DataFuncs
{
    /// <summary>
    /// Queries the data store based on the passed request.
    /// </summary>
    /// <param name="tablename">The name of the requested table or view</param>
    /// <param name="where">Filter options in the form columnname1 = value AND columnname2 = value2</param>
    /// <param name="orderby">Sort options in the form columnname1,columnname2 desc</param>
    /// <returns></returns>
    public static DataTable GetAnyTable(string tablename,string where = "", string orderby = "")
    {
        string strSQL;
        DataTable readerdata = new DataTable(tablename);

        strSQL = "select * From " + tablename;
        if (where != "" ) 
        {
            strSQL = strSQL + " WHERE " + where;
            if (orderby != "")
            {
                strSQL = strSQL + " ORDER BY " + orderby;
            }
        }

        using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["RafikiDBConn"].ConnectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(strSQL, connection))
            {

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    readerdata.Load(reader);
                    return readerdata;
                }
            }

            connection.Close();
        }

    }

    /// <summary>
    /// Queries the data store based on the passed request.
    /// </summary>
    /// <param name="tablename">The name of the requested table or view</param>
    /// <param name="where">Filter options in the form columnname1 = value AND columnname2 = value2</param>
    /// <param name="orderby">Sort options in the form columnname1,columnname2 desc</param>
    /// <returns></returns>
    public static DataTable GetAnyTableCoreData(string tablename, string where = "", string orderby = "")
    {
        string strSQL;
        DataTable readerdata = new DataTable(tablename);

        strSQL = "select * From " + tablename;
        if (where != "")
        {
            strSQL = strSQL + " WHERE " + where;
            if (orderby != "")
            {
                strSQL = strSQL + " ORDER BY " + orderby;
            }
        }

        using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CoreDataConn"].ConnectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(strSQL, connection))
            {

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    readerdata.Load(reader);
                    return readerdata;
                }
            }

            connection.Close();
        }

    }
    /// <summary>
    /// Executes the specified stored procedure and returns the recordset as a data table.
    /// </summary>
    /// <param name="spname">Name of the stored procedure</param>
    /// <param name="variables">Dictionary of parameter names and values</param>
    /// <returns></returns>
    public static DataTable GetSQLsp(string spname, Dictionary<string,string> variables)
    {
        string strSQL;
        DataTable readerdata = new DataTable(spname);

        strSQL = spname;

        using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["RafikiDBConn"].ConnectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(strSQL, connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                foreach (KeyValuePair<string, string> item in variables)
                {
                    command.Parameters.Add(item.Key, SqlDbType.VarChar).Value = item.Value;
                }
                command.CommandTimeout = 60000;
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    readerdata.Load(reader);
                    return readerdata;
                }
            }

            connection.Close();
        }
    }

    /// <summary>
    /// Runs the update or insert query specified in strSQL and returns the number of rows affected
    /// </summary>
    /// <param name="strSQL"></param>
    /// <param name="parameters"></param>
    /// <returns></returns>
    public static int ExecuteSQL(string strSQL)
    {

        using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CoreDataConn"].ConnectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(strSQL, connection))
            {
                return command.ExecuteNonQuery();
            }
            connection.Close();
        }
    }

    /// <summary>
    /// Receives "select" query syntax and returns the result.
    /// </summary>
    /// <param name="query"></param>
    /// <returns></returns>
    public static DataTable ValRunReadQuery(string query)
    {
        string strSQL;
        DataTable readerdata = new DataTable("QueryData");

        strSQL = query;

        using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CoreDataConn"].ConnectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(strSQL, connection))
            {

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    readerdata.Load(reader);
                    return readerdata;
                }
            }
            connection.Close();
        }
    }

    /// <summary>
    /// Writes the contents of a DataTable to XML.  Compared to GetXML, this function will include values that are null.
    /// Code copied from http://codeverge.com/asp.net.xml-datasource/dataset.writexml/517428#sthash.HJEZDHlC.dpuf
    /// </summary>
    /// <param name="table">The table of data</param>
    /// <returns></returns>
    public static XmlDocument WriteDataTable(DataTable table)
    {
      XmlDocument xml = new XmlDocument();
      xml.LoadXml("<Table/>");
      string NewTableName = table.TableName.Replace("(", "_").Replace(")", "_").Replace(";", "_").Replace(".","_");
      foreach (DataRow dr in table.Rows)
      {
          XmlElement xmlRow = xml.CreateElement(NewTableName);
          foreach (DataColumn col in table.Columns)
          {
              XmlElement xmlCol = xml.CreateElement(col.ColumnName);
              // if the value is null, the element is created, but no value is assigned.
              if(dr[col] != System.DBNull.Value)
              {
                  xmlCol.InnerText = dr[col].ToString();
              }
              xmlRow.AppendChild(xmlCol);
          }
          xml.DocumentElement.AppendChild(xmlRow);
      }
      return xml;
    }

    /// <summary>
    /// Some functions return a JObject that contains the desired JArray of data.
    /// This function extracts the JArray and returns it.
    /// </summary>
    /// <param name="sourceData">JObject that contains a JArrray</param>
    /// <returns>Returns the extracted JArray or the data converted to a JArray, 
    ///          unless there is no data, in which case it returns null.</returns>
    public static JArray StripToJSONArray(JObject sourceData)
    {
        // This set of conditionals checks two levels down to make sure an object exists at that level,
        // then returns everything from that level down, effectively stripping off the top level value.
        if (sourceData != null)
        {
            if (sourceData.First != null)
            {
                if (sourceData.First.First != null)
                {
                    // Since the returned type must be an array, this checks to see if the jtoken is an array.  If not
                    // it creates an array and makes the jtoken the first object in it.
                    if (sourceData.First.First.Type == JTokenType.Array)
                    {
                        return (JArray)sourceData.First.First;
                    }
                    else
                    {
                        return new JArray(sourceData.First.First);
                    }
                }
            }
        }
        return null;
    }


    public static DataTable ExecuteSQLsp(string spname, Dictionary<string, string> variables)
    {
        string strSQL;
        DataTable readerdata = new DataTable(spname);

        strSQL = spname;

        using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["RafikiDBConn"].ConnectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(strSQL, connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                foreach (KeyValuePair<string, string> item in variables)
                {
                    command.Parameters.Add(item.Key, SqlDbType.VarChar).Value = item.Value;
                }

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    readerdata.Load(reader);
                    return readerdata;
                }
            }

            connection.Close();
        }
    }

    /// <summary>
    /// Executes the ReadDataFromDB stored procedure.
    /// </summary>
    /// <param name="spCommandName">The name of the view or other command to be passed to ReadDataFromDB</param>
    /// <param name="spCommandType">The type of command, defaults to "View"</param>@P1
    /// <param name="variables">The parameters to be passed to ReadDataFromDB</param>
    /// <returns></returns>
    public static DataTable ReadDataFromDB(int RequestorEntityID,string spCommandName, Dictionary<string, string> variables = null, string spCommandType = "View")
    {
        string strSQL;
        DataTable readerdata = new DataTable(spCommandName);

        strSQL = "ReadDataFromDB";

        using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["RafikiDBConn"].ConnectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(strSQL, connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                if(variables != null)
                {
                    foreach (KeyValuePair<string, string> item in variables)
                    {
                        command.Parameters.Add(item.Key, SqlDbType.VarChar).Value = item.Value;
                    }
                }
                command.Parameters.Add("CommandName",SqlDbType.NVarChar).Value = spCommandName;
                command.Parameters.Add("CommandType", SqlDbType.NVarChar).Value = spCommandType;
                command.Parameters.Add("RequestorEntityID", SqlDbType.NVarChar).Value = RequestorEntityID;
                //command.Parameters.Add("RequestorEntityID", SqlDbType.NVarChar).Value = BasicFuncs.GetPersonIDFromUsername(UserName);
                command.CommandTimeout = 100000;
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    readerdata.Load(reader);
                    return readerdata;
                }
            }

            connection.Close();
        }
    }

    /// <summary>
    /// This is useful for fetching ONE specific value from a Table
    /// </summary>
    /// <param name="tablename"></param>
    /// <param name="whereclause"></param>
    /// <param name="columnname"></param>
    /// <returns></returns>
    public static string ReadDataColumn(string tablename, string whereclause, string columnname)
    {
        DataTable TempTable = DataFuncs.GetAnyTable(tablename, whereclause, "");
        if (TempTable.Rows.Count > 0)
        {
            return TempTable.Rows[0][columnname].ToString();
        }
        else
        {
            return "";
        }
    }

    public static int WriteDataToDB(int RequestorEntityID, string CommandType, Dictionary<string, string> WriteData, int EntityID = -1)
    {
        // Convert Dictionary List to JSON
        // Call StoreProcedure

        return 0;
    }

    /// <summary>
    /// Executes the ReadDataFromDB_New stored procedure.
    /// </summary>
    /// <param name="spCommandName">The name of the view or other command to be passed to ReadDataFromDB</param>
    /// <param name="spCommandType">The type of command, defaults to "View"</param>@P1
    /// <param name="variables">The parameters to be passed to ReadDataFromDB</param>
    /// <returns></returns>
    public static DataTable ReadDataFromDB_New(int RequestorEntityID, string spCommandName, Dictionary<string, string> variables = null, string spCommandType = "View")
    {
        string strSQL;
        DataTable readerdata = new DataTable(spCommandName);

        strSQL = "ReadDataFromDB_New";

        using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["RafikiDBConn"].ConnectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(strSQL, connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                if (variables != null)
                {
                    foreach (KeyValuePair<string, string> item in variables)
                    {
                        command.Parameters.Add(item.Key, SqlDbType.VarChar).Value = item.Value;
                    }
                }
                command.Parameters.Add("RequestedChain", SqlDbType.NVarChar).Value = spCommandName;
                command.Parameters.Add("CommandType", SqlDbType.NVarChar).Value = spCommandType;
                command.Parameters.Add("RequestorEntityID", SqlDbType.NVarChar).Value = RequestorEntityID;
                //command.Parameters.Add("RequestorEntityID", SqlDbType.NVarChar).Value = BasicFuncs.GetPersonIDFromUsername(UserName);
                command.CommandTimeout = 100000;
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    readerdata.Load(reader);
                    return readerdata;
                }
            }

            connection.Close();
        }
    }

}